import warnings
import re
import time

from pandas.io.sql import SQLTable, SQLiteDatabase
from pandas.io.sql import _get_valid_sqlite_name, _SQL_TYPES, _SAFE_NAMES_WARNING, is_list_like
from pandas.core.dtypes.common import (is_dict_like)
import pandas._libs.lib as lib
from builtins import map


class SQLiteTable(SQLTable):
    """
    Patch the SQLTable for fallback support.
    Instead of a table variable just use the Create Table statement.
    """
    _indexs_stmt = list()

    def __init__(self, *args, **kwargs):
        # GH 8341
        # register an adapter callable for datetime.time object
        import sqlite3
        # this will transform time(12,34,56,789) into '12:34:56.000789'
        # (this is what sqlalchemy does)
        sqlite3.register_adapter(time, lambda _: _.strftime("%H:%M:%S.%f"))
        super(SQLiteTable, self).__init__(*args, **kwargs)
        self._handle_indexs(*args, **kwargs)

    def _handle_indexs(self, name, pandas_sql_engine, frame=None, index=True,if_exists='fail',
                       prefix='pandas', index_label=None, schema=None, keys=None, dtype=None):
        self.index_label = index_label


    def sql_schema(self):
        return str(";\n".join(self.table))

    def _execute_create(self):
        with self.pd_sql.run_transaction() as con:
            for stmt in self.table:
                con.execute(stmt)

            if self.index_label is not None and isinstance(self.index_label, list):
                escape = _get_valid_sqlite_name
                for item in self.index_label:
                    self._indexs_stmt.append("CREATE INDEX " + escape("ix_" + self.name + "_" + item) +
                        " ON " + escape(self.name) + " (" + item + ")")
                for stmt in self._indexs_stmt:
                    con.execute(stmt)

    def insert_statement(self):
        names = list(map(str, self.frame.columns))
        wld = '?'  # wildcard char
        escape = _get_valid_sqlite_name

        if self.index is not None:
            [names.insert(0, idx) for idx in self.index[::-1]]

        bracketed_names = [escape(column) for column in names]
        col_names = ','.join(bracketed_names)
        wildcards = ','.join([wld] * len(names))
        insert_statement = \
            u'INSERT INTO {table} ({columns}) VALUES ({wld})'.format(
                table=escape(self.name), columns=col_names, wld=wildcards)
        return insert_statement

    def _execute_insert(self, con, keys, data_iter):
        data_list = list(data_iter)
        con.executemany(self.insert_statement(), data_list)

    def _create_table_setup(self):
        """
        Return a list of SQL statements that creates a table reflecting the
        structure of a DataFrame.  The first entry will be a CREATE TABLE
        statement while the rest will be CREATE INDEX statements.
        """
        column_names_and_types = self._get_column_names_and_types(
            self._sql_type_name
        )

        pat = re.compile(r'\s+')
        column_names = [col_name for col_name, _, _ in column_names_and_types]
        if any(map(pat.search, column_names)):
            warnings.warn(_SAFE_NAMES_WARNING, stacklevel=6)

        escape = _get_valid_sqlite_name

        create_tbl_stmts = [escape(cname) + ' ' + ctype
                            for cname, ctype, _ in column_names_and_types]

        if self.keys is not None and len(self.keys):
            if not is_list_like(self.keys):
                keys = [self.keys]
            else:
                keys = self.keys
            cnames_br = ", ".join(escape(c) for c in keys)
            create_tbl_stmts.append(
                "CONSTRAINT \'{tbl}_pk\' PRIMARY KEY ({cnames_br})".format(
                    tbl=self.name, cnames_br=cnames_br))

        create_stmts = ["CREATE TABLE " + escape(self.name) + " (\n" +
                        ',\n  '.join(create_tbl_stmts) + "\n)"]

        ix_cols = [cname for cname, _, is_index in column_names_and_types
                   if is_index]
        if len(ix_cols):
            cnames = "_".join(ix_cols)
            cnames_br = ",".join(escape(c) for c in ix_cols)
            create_stmts.append(
                "CREATE INDEX " + escape("ix_" + self.name + "_" + cnames) +
                "ON " + escape(self.name) + " (" + cnames_br + ")")

        return create_stmts

    def _sql_type_name(self, col):
        dtype = self.dtype or {}
        if col.name in dtype:
            return dtype[col.name]

        # Infer type of column, while ignoring missing values.
        # Needed for inserting typed data containing NULLs, GH 8778.
        col_type = lib.infer_dtype(col, skipna=True)

        if col_type == 'timedelta64':
            warnings.warn("the 'timedelta' type is not supported, and will be "
                          "written as integer values (ns frequency) to the "
                          "database.", UserWarning, stacklevel=8)
            col_type = "integer"

        elif col_type == "datetime64":
            col_type = "datetime"

        elif col_type == "empty":
            col_type = "string"

        elif col_type == "complex":
            raise ValueError('Complex datatypes not supported')

        if col_type not in _SQL_TYPES:
            col_type = "string"

        return _SQL_TYPES[col_type]


class MySQLiteDatabase(SQLiteDatabase):
    def __init__(self, con, is_cursor=False):
        self.is_cursor = is_cursor
        self.con = con

    def init_table(self, frame, name, if_exists='fail', schema=None, dtype=None, keys=None, index=None, index_label=None):

        if dtype and not is_dict_like(dtype):
            dtype = {col_name: dtype for col_name in frame}

        if dtype is not None:
            for col, my_type in dtype.items():
                if not isinstance(my_type, str):
                    raise ValueError('{column} ({type!s}) not a string'.format(
                        column=col, type=my_type))

        table = SQLiteTable(name, self, frame=frame, index=False,if_exists=if_exists,
                            dtype=dtype, schema=schema, keys=keys, index_label=index_label)

        table.create()
